/**
 * Copyright (c) 2017,安徽皖通科技股
 * 份有限公司 All rights reserved.
 *
 * @data:2017年10月16日
 * @author:MaShibo
 */
package com.wttech.dzzf.process.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.annotation.Resource;

import cn.com.taiji.sdk.model.comm.protocol.transaction.RechargeUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.user.CardUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.user.UserUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.user.VehicleUploadRequest;
import com.wtkj.vo.dzzf.base.kfwd.FundAccount;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.wtkj.dao.impl.BaseDataTemplate;
import com.wtkj.dao.impl.ListResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfwd.TransIn;
import org.springframework.util.CollectionUtils;

/**
 * @author MaShibo
 * @date 2017年10月16日
 */
@Repository
public class RechargeInfoDao extends BaseDataTemplate {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate testJdbcTemplate;

    @Resource(name = "dzzfJdbcTemplate")
    private JdbcTemplate dzzfJdbcTemplate;

    @SuppressWarnings("unchecked")
    public List<TransIn> queryRechargeInfos(List<String> dateStrs, int startRow, int endRow) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM ( ");
        sql.append(StringUtils.replace(buildSQL(dateStrs).toString(), "*", " TR.*, ROWNUM AS RM "));
        sql.append(") ");
        sql.append(" WHERE RM >= ? ");
        sql.append(" AND RM <= ? ");
        List<Object> params = new ArrayList<>();
        dateStrs.forEach(dateStr -> {
            params.add(dateStr);
            params.add(dateStr);
        });
        params.add(startRow);
        params.add(endRow);
        return (List<TransIn>) dzzfJdbcTemplate.query(sql.toString(), params.toArray(),
                new ListResultSetExtractor(new TransIn()));
    }

    public int queryTestRechargeNum(){
        return testJdbcTemplate.queryForObject("SELECT COUNT(1) FROM BS_RECHARGE ", Integer.class);
    }

    /**
     * 查询充值记录总数量
     *
     * @param dateStrs 充值日期集合
     * @return 充值记录数量
     */
    public int queryRechargeInfosNum(List<String> dateStrs) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(1) FROM ( ");
        sql.append(buildSQL(dateStrs));
        sql.append(" ) ");
        List<Object> params = new ArrayList<>();
        dateStrs.forEach(dateStr -> {
            params.add(dateStr);
            params.add(dateStr);
        });
        return dzzfJdbcTemplate.queryForObject(sql.toString(), Integer.class, params.toArray());
    }

    @SuppressWarnings("unchecked")
    public List<TransIn> queryRechargeInfos(String dateStr) {
        String sql = " SELECT * " +
                "   FROM TR_TRANSIN TR " +
                "  WHERE 1 = 1 " +
                "  AND TR.STATE = 1 " +
                " AND TR.OPTON >= TO_DATE(?, 'yyyy-MM-dd') " +
                " AND TR.OPTON < TO_DATE(?, 'yyyy-MM-dd') + 1 " +
                " ORDER BY TR.OPTON ";
        return (List<TransIn>) dzzfJdbcTemplate.query(sql, new Object[]{dateStr, dateStr},
                new ListResultSetExtractor(new TransIn()));
    }

    private StringBuilder buildSQL(List<String> dateStrs) {
        StringBuilder sql = new StringBuilder();
        String[] dateArr = new String[dateStrs.size()];
        sql.append(" SELECT * ");
        sql.append("   FROM TR_TRANSIN TR ");
        sql.append("  WHERE 1 = 1 ");
        sql.append("  AND TR.STATE = 1 ");
//        sql.append(" AND TR.TOPCLASS = '6101' ");
        sql.append(" AND (");

        Arrays.fill(dateArr, "( " + "TR.OPTON >= TO_DATE(?, 'yyyy-MM-dd')" + " AND " +
                " TR.OPTON < TO_DATE(?, 'yyyy-MM-dd') + 1 " + " )");
        sql.append(StringUtils.join(dateArr, " OR "));
        sql.append(" ) ");
        return sql;
    }



    public List<String> queryRechargeCardNo(int startRow, int endRow) {
        final String sql =
                "SELECT cardid " +
                        "  FROM (SELECT a.cardid, rownum AS rm FROM bs_recharge a) " +
                        " WHERE rm >= ? " +
                        "   AND rm <= ?";
        return testJdbcTemplate.query(sql, (rs, rowNum) -> rs.getString("CARDID"), startRow, endRow);
    }

    public List<RechargeUploadRequest> queryRechargeUploadInfos(int startRow, int endRow) {
        final String sql =
                "SELECT * " +
                        "  FROM (SELECT a.*, rownum AS rm FROM bs_recharge a) " +
                        " WHERE rm >= ? " +
                        "   AND rm <= ?";
        return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(RechargeUploadRequest.class), startRow, endRow);
    }

    public UserUploadRequest queryUserUploadInfo(String userId){
        final String sql = "SELECT * FROM BS_USER A WHERE A.ID=? ";
        List<UserUploadRequest> userUploadRequests = testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserUploadRequest.class), userId);
        return CollectionUtils.isEmpty(userUploadRequests) ? null : userUploadRequests.get(0);
    }

    public VehicleUploadRequest queryVehicleUploadInfo(String vehicelId){
        final String sql = "SELECT * FROM BS_VEHICLE A WHERE A.ID = ? ";
        List<VehicleUploadRequest> vehicleUploadRequests = testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(VehicleUploadRequest.class), vehicelId);
        return CollectionUtils.isEmpty(vehicleUploadRequests) ? null : vehicleUploadRequests.get(0);
    }

    public CardUploadRequest queryCardUploadInfo(String cardId){
        final String sql = "SELECT * FROM BS_CARD A WHERE A.ID=? ";
        List<CardUploadRequest> cardUploadRequests = testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(CardUploadRequest.class), cardId);
        return CollectionUtils.isEmpty(cardUploadRequests) ? null : cardUploadRequests.get(0);
    }

    public int queryRechargeInfoNum(String rechargeId){
        final String sql = "SELECT COUNT(1) FROM BS_RECHARGE A WHERE A.ID = ? ";
        return testJdbcTemplate.queryForObject(sql, Integer.class, rechargeId);
    }

    public static void main(String[] args) {
        System.out.println(new RechargeInfoDao().queryRechargeInfosNum(Arrays.asList("2017-11-02", "2017-11-11")));
    }

}
